<?php

class RepController extends Zend_Controller_Action{
    
    public function indexAction(){
        //$this->view->assign('title','Login');
        //$this->view->assign('description','Successfully');
        $request=  $this->getRequest();
        $auth=  Zend_Auth::getInstance();
        if(!$auth->hasIdentity()){
            $this->_redirect('/rep/login-form');
            
        }
        else{
            $this->_redirect('/rep/user-page');
        }
    }
    public function loginFormAction(){
        $request = $this->getRequest();
        
        $this->view->assign('action',$request->getBaseURL()."/rep/auth");
        $this->view->assign('title','Login Form');
        $this->view->assign('username','UserName');
        $this->view->assign('password','PassWord');
        
    }
    public function authAction(){
        
        $request = $this->getRequest();
        //1.Goi ket noi voi Zend Db
       $registry = Zend_Registry::getInstance();
        $DB = $registry['connectDB'];
      //  $DB=  Zend_Registry::get('connectDB');
        //2. Khoi tao Zend Autho
        $auth = Zend_Auth::getInstance();
        
         //3. Khai bao bang va 2 cot se su dung so sanh trong qua trinh login
        $authAdapter = new Zend_Auth_Adapter_DbTable($DB);
        $authAdapter->setTableName('tbluser')
                    ->setIdentityColumn('username')
                    ->setCredentialColumn('password');
        
       //4. Lay gia tri duoc gui qua tu FORM
        $uname=$request->getParam('username');
        $paswd=$request->getParam('password');
        
        //5. Dua vao so sanh voi du lieu khai bao o muc 3
        $authAdapter->setIdentity($uname);
        $authAdapter->setCredential(md5($paswd));
        
        //7. Lay ket qua truy van
        $result=$auth->authenticate($authAdapter);
        $flag=FALSE;
        
        if($result->isValid()){
            
            //8. Lay nhung du lieu can thiet trong bang users neu login thanh cong
            $data=$authAdapter->getResultRowObject(null,array('password'));
            
            
             //9. Luu  nhung du lieu cua member vao session
            $auth->getStorage()->write($data);
            
           
            
            $this->view->assign('title','Login Success');
            $flag=TRUE;
            
            $session= new Zend_Session_Namespace('login');
            
            $user=$auth->getIdentity();
            $tblRepID=$user->tblRepID;
            $repname=$user->repName;
            $JobCategoryID=$user->tblJobCategoryID;
            $XsisClientID=$user->tblXsisClientID;
            $XsisClient=  $this->getXsisClient($XsisClientID);
            //get information about Job_name,level
            $result=  $this->getJobcategory($JobCategoryID);
            $level=$result[0]['Level'];
            $jobDescription=$result[0]['Description'];
            //link logout
            $saleRouteID=$user->saleRouteID;
            $logoutUrl=$request->getBaseURL().'/rep/logout';
        
            $user_array = array ("RepID" => $tblRepID ,
                     "repName" => $repname,
                     "level" => $level,
                    "jobDescription"=>$jobDescription,
                    "saleRouteID"=> $saleRouteID,
                    "XsisClientID" => $XsisClientID,
                    "XsisClient" => $XsisClient,
                     "logout" => $logoutUrl);
        
            //    Zend_Registry::set('userInformation', $user_array);
              $session->userInformation=$user_array; 
             $this->_redirect('/rep/user-page');
           //$this->_redirect('/user/list');
            
        }
        else
        {
            $this->_redirect('/rep/login-form');
            //$this->_redirect('/user/list');
        }
         
        //add more information about user into Registry
        
        
    }
    public function userPageAction(){
        $auth= Zend_Auth::getInstance();
       $registry=  Zend_Registry::getInstance();
       
       $DB = $registry['connectDB'];
      $userProfile= new Zend_Session_Namespace('login');
       $auth_user=$userProfile->userInformation;
       
        if(!$auth->hasIdentity() ){
            $this->_redirect('/rep/login-form');
            
        }
        
        //get params are sended from client to server
        $request=  $this->getRequest();
      //  $status=  $request->getParam('cboStatus');
       
        if(isset($_POST['cboStatus'])){
            $status=$_POST['cboStatus'];
        }
       else{
           $status='none';
           $day=30;
           $est=0;
          
        }
        
       
       if($status=='Next')
            {
                $est=$request->getParam('txtest');
                $day=$request->getParam('txtstat');
               
                if(!is_null($est))
                {
                    if(!is_numeric($est))
                    {
                        $est=0;
                    }
                }
                else
                {
                    $est=0;
                }
            }
            else if($status=='Stat')
            {
               $day=$request->getParam('txtstat');
               $est=$request->getParam('txtest');
               
               if(!is_null($day))
                {
                    if(!is_numeric($day))
                    {
                        $day=30;
                    }
                }
                else
                {
                    $day=30;
                }
            }
            
       
        
        
        //get information about user login
        $user=$auth->getIdentity();
       $repname=$auth_user['repName'] ;// $repname=$user->repName;
        
        //$JobCategoryID=$user->tblJobCategoryID;
        //$result=  $this->getJobcategory($JobCategoryID);
        $level=$auth_user['level']; //$level=$result[0]['Level'];
        $description = $auth_user['jobDescription']; //$description=$result[0]['Description'];
        
        $logoutUrl=$auth_user['logout']; //$logoutUrl=$request->getBaseURL().'/rep/logout';
        
        //get information about xsisClient
        $tblXsisClientID=$auth_user['XsisClientID']; //$tblXsisClientID = $user->tblXsisClientID;
        $saleRouteID=$auth_user['saleRouteID']; //$saleRouteID = $user->saleRouteID;
        $XsisClient=$auth_user['XsisClient']; //$XsisClient =  $this->getXsisClient($tblXsisClientID);
        $tblSaleGroupID = $user->saleGroupID;
    
        if($level==4)
        {
            $data_late=  $this->getData_Later_level4($tblXsisClientID);
            $data_next= $this->getData_Next_level4($tblXsisClientID,$est);
            $data_stat=  $this->getData_Stat_level4($tblXsisClientID,$day);
        }
        else if($level==3)
        {
            $data_late = $this->getData_Later_level3($tblSaleGroupID, $tblXsisClientID);
            $data_next=  $this->getData_Next_level3($tblSaleGroupID, $tblXsisClientID,$est);
            $data_stat=  $this->getData_Stat_level3($tblSaleGroupID, $tblXsisClientID,$day);
        }
        else if($level==2 || $level==1)
        {
            $data_late=  $this->getData_Later_level2($saleRouteID, $tblXsisClientID);
            $data_next=  $this->getData_Next_level2($saleRouteID, $tblXsisClientID,$est);
            $data_stat=  $this->getData_Stat_level2($saleRouteID, $tblXsisClientID,$day);
                    
        }
        
       
        $this->view->assign('data_late',$data_late);
        $this->view->assign('data_next',$data_next);
        $this->view->assign('data_stat',$data_stat);
        $this->view->assign('repname',$repname);
        $this->view->assign('estimate',$est);
        $this->view->assign('dayStat',$day);
        $this->view->assign('status',$status);
       
        $this->view->XsisClientID=$tblXsisClientID;
        $this->view->assign('action',$request->getBaseURL()."/rep/user-page");
        $this->view->assign('level',$level);
        $this->view->job = $description;
        $this->view->assign('logout',$logoutUrl);
        $this->view->xsisclient=$XsisClient[0]['name'];
        
    }
    public function logoutAction(){
        $auth=  Zend_Auth::getInstance();
        $auth->clearIdentity();
        $registry=  Zend_Registry::getInstance();
        unset($registry['userInformation']);
        $this->_redirect('/rep/login-form');
    }
    public function historyAction(){
    //    $this->view->layout()->disableLayout();
      //  $this->_helper->viewRenderer->setNoRender(true);
        
       // $auth= Zend_Auth::getInstance();
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
      $userProfile= new Zend_Session_Namespace('login');
       $auth_user=$userProfile->userInformation;
        
        $request=  $this->getRequest();
    //   $XsisClientID=$request->getParam('XsisClientID');
        //get information about user
        
      // $user=$auth->getIdentity();
       //$XsisClientID=$user->tblXsisClientID;
       $XsisClientID=$auth_user['XsisClientID'];
       // $saleRouteID=$user->saleRouteID;
      
        $group=  $this->getGroup($XsisClientID);
        
        $rep=  $this->getRep($XsisClientID);
        $this->view->assign('group',$group); 
        $this->view->rep=$rep;
          
        
    }
    public function getRouteAction(){
        $auth=  Zend_Auth::getInstance();
        $DB=  Zend_Registry::get('connectDB');
        
        $request=  $this->getRequest();
        $user=$auth->getIdentity();
         $XsisClientID=$user->tblXsisClientID;
         $saleGroupID=  $request->getParam('saleGroupID');
         
         $route=  $this->getRoute($saleGroupID, $XsisClientID);
         
         $this->view->assign('route',$route);
                 
    }
    public function getShopAction(){
        $auth=  Zend_Auth::getInstance();
        $DB=  Zend_Registry::get('connectDB');
        $request=  $this->getRequest();
        
        $user=$auth->getIdentity();
        $XsisClientID=$user->tblXsisClientID;
        $saleRouteID=$request->getParam('saleRouteID');
        
        $shop=  $this->getShop($saleRouteID, $XsisClientID);
        
        $this->view->shop=$shop;
    }
    
    public function getHistoryLateFineAction(){
        $auth=  Zend_Auth::getInstance();
        $DB=  Zend_Registry::get('connectDB');
        
        $request=  $this->getRequest();
        $user=$auth->getIdentity();
        $XsisClientID=$user->tblXsisClientID;
        
       $saleGroupID=$request->getParam('saleGroupID');
        
        $saleRouteID=$request->getParam('saleRouteID');
      //  $saleGroupID=1;
        $date_now= date("Y-m")."-01";
        
        if(!is_null($saleGroupID)){
           
           for($i=1;$i<13;$i++){
                $sumLateFine=0;
               
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
               $sql_late_fine="select * from tblsummarydaily s inner join tblsaleroute r on s.saleRouteID=r.saleRouteID 
                     where s.tblXsisClientID='".$XsisClientID."' and r.saleGroupID='".$saleGroupID."' 
                     and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_late_fine);
               
               if (count($result)>0){
                   for($j=0;$j<count($result);$j++){
                       $sumLateFine+=$result[$j]['lateFine'];
                   }
               }
               
             
               
               $total[$i]['lateFine']=$sumLateFine;
              
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
          
           
        }
        if(!is_null($saleRouteID)){
             for($i=1;$i<13;$i++){
                $sumLateFine=0;
               
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
               $sql_late_fine="select * from tblsummarydaily s
                     where s.tblXsisClientID='".$XsisClientID."' and s.saleRouteID='".$saleRouteID."' 
                     and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_late_fine);
               
               if (count($result)>0){
                   for($j=0;$j<count($result);$j++){
                       $sumLateFine+=$result[$j]['lateFine'];
                   }
               }
               
             
               
               $total[$i]['lateFine']=$sumLateFine;
              
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
        }
        
         $this->view->historylatefine=$total;
                
    }
    public function getHistoryVisitAction(){
        $auth=  Zend_Auth::getInstance();
        $DB=  Zend_Registry::get('connectDB');
        $request=  $this->getRequest();
        $user=$auth->getIdentity();
        $XsisClientID=$user->tblXsisClientID;
        
        $saleGroupID=$request->getParam('saleGroupID');
        $saleRouteID=$request->getParam('saleRouteID');
        $RepID=$request->getParam('RepID');
        $ShopID=$request->getParam('ShopID');
        
        
         if(!is_null($saleGroupID)){
           
           for($i=1;$i<13;$i++){
               
                $sumvisit=0;
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
             
               
               $sql_visit="select count(*) as sum from tbltransactionraw t inner join tblshop s on t.tblShopID=s.tblShopID
                            inner join tblsaleroute r on s.saleRouteID=r.saleRouteID 
                            where s.tblXsisClientID='".$XsisClientID."' and r.saleGroupID='".$saleGroupID."' 
                            and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_visit);
               $sumvisit+=$result[0]['sum'] ;
               
               $total[$i]['visit']=$sumvisit;
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
          
           
        }
         if(!is_null($saleRouteID)){
           
           for($i=1;$i<13;$i++){
               
                $sumvisit=0;
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
             
               
                $sql_visit="select count(*) as sum from tbltransactionraw t inner join tblshop s on t.tblShopID=s.tblShopID
                            where s.tblXsisClientID='".$XsisClientID."' and s.saleRouteID='".$saleRouteID."' 
                            and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_visit);
               $sumvisit+=$result[0]['sum'] ;
               
               $total[$i]['visit']=$sumvisit;
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
          
           
        }
        if(!is_null($RepID)){
           
           for($i=1;$i<13;$i++){
               
                $sumvisit=0;
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
             
               
                $sql_visit="select count(*) as sum from tbltransactionraw t inner join tblshop s on t.tblShopID=s.tblShopID
                            where s.tblXsisClientID='".$XsisClientID."' and t.tblRepID='".$RepID."' 
                            and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_visit);
               $sumvisit+=$result[0]['sum'] ;
               
               $total[$i]['visit']=$sumvisit;
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
          
           
        }
         if(!is_null($ShopID)){
           
           for($i=1;$i<13;$i++){
               
                $sumvisit=0;
               $day_start=date("Y-m",  strtotime("- ".$i." month"))."-01";
               $day_end=date("Y-m",  strtotime("- ".$i." month"))."-31";
               $month=date("m",  strtotime($day_start));
               $year=date("Y",  strtotime($day_start));
               
             
               
                $sql_visit="select count(*) as sum from tbltransactionraw t inner join tblshop s on t.tblShopID=s.tblShopID
                            where s.tblXsisClientID='".$XsisClientID."' and s.tblShopID='".$ShopID."' 
                            and date between '".$day_start."' and '".$day_end."'";
               $result=$DB->fetchAll($sql_visit);
               $sumvisit+=$result[0]['sum'] ;
               
               $total[$i]['visit']=$sumvisit;
               $total[$i]['month']=$month;
               $total[$i]['year']=$year;
           }     
          
           
        }
        $this->view->historyvisit=$total;
    }

    public function getXsisClient($tblxsisclientID)
    {
        $registry = Zend_Registry::getInstance();
        $DB = $registry['connectDB'];
        
        $sql = "select * from tblxsisclient where tblxsisclientid='".$tblxsisclientID."'";
        $result = $DB->fetchAll($sql);
        
        return $result;
    }
    public function getJobcategory($JobCategoryID)
    {
        $registry= Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        
        $sql="select * from tbljobcategory where tblJobCategoryID='".$JobCategoryID."'";
        
        $result = $DB->fetchAll($sql);
        
        return $result;
        
    }
    public function getData_Later_level2($saleRouteID,$tblXsisClientID)
    {
           $data=  $this->getShop2($saleRouteID, $tblXsisClientID);
           
           $totalRouteShopProblem=0;
        $totalRouteShopNoProblem=0;
        
        for($i=0;$i<count($data);$i++){
            $totalRouteShopNoProblem=0;
            $totalRouteShopProblem=0;
            for($j=0;$j<count($data[$i]['shop']);$j++){
                if($data[$i]['shop'][$j]['VisitProblem']=="Yes")
                {
                    $totalRouteShopProblem++;
                }
                else if($data[$i]['shop'][$j]['VisitProblem']=="No")
                {
                    $totalRouteShopNoProblem++;
                }
            }
            $data[$i]['ShopNoProblem']=$totalRouteShopNoProblem;
            $data[$i]['ShopProblem']=$totalRouteShopProblem;
        }
        
           return $data;
           
    }


    public function getData_Later_level3($tblSaleGroupID,$tblXsisClientID)
    {
        $data=  $this->getRoute($tblSaleGroupID, $tblXsisClientID);
        
        
        
        for($i=0;$i<count($data);$i++)
        {
            $data[$i]['shop']=  $this->getShop($data[$i]['saleRouteID'], $tblXsisClientID);
            
            
        }
        
        //get sum shop,how many shop have problem,how many shop no problem
        $totalRouteShopProblem=0;
        $totalRouteShopNoProblem=0;
        
        for($i=0;$i<count($data);$i++){
            $totalRouteShopNoProblem=0;
            $totalRouteShopProblem=0;
            for($j=0;$j<count($data[$i]['shop']);$j++){
                if($data[$i]['shop'][$j]['VisitProblem']=="Yes")
                {
                    $totalRouteShopProblem++;
                }
                else if($data[$i]['shop'][$j]['VisitProblem']=="No")
                {
                    $totalRouteShopNoProblem++;
                }
            }
            $data[$i]['ShopNoProblem']=$totalRouteShopNoProblem;
            $data[$i]['ShopProblem']=$totalRouteShopProblem;
        }
        
        return $data;
        
    }
   
    public function getData_Later_level4($tblXsisClientID)
    {
        $data=  $this->getGroup($tblXsisClientID);
        
        for($i=0;$i<count($data);$i++){
            $data[$i]['route']=  $this->getRoute($data[$i]['saleGroupID'],$tblXsisClientID);
           for($j=0;$j<count($data[$i]['route']);$j++){
                $data[$i]['route'][$j]['shop']=  $this->getShop($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID);
            }
        }
        
        //get sum shop,how many shop have problem,how many shop no problem
        $totalGroupShopProblem=0;
        $totalGroupShopNoProblem=0;
        $totalRouteShopProblem=0;
        $totalRouteShopNoProblem=0;
        for($i=0;$i<count($data);$i++){
            $totalGroupShopProblem=0;
            $totalGroupShopNoProblem=0;
            for($j=0;$j<count($data[$i]['route']);$j++){
                $totalRouteShopProblem=0;
                $totalRouteShopNoProblem=0;
                for($k=0;$k<count($data[$i]['route'][$j]['shop']);$k++){
                    if($data[$i]['route'][$j]['shop'][$k]['VisitProblem']=='Yes'){
                        $totalRouteShopProblem++;
                        $totalGroupShopProblem++;
                    }
                    else if ($data[$i]['route'][$j]['shop'][$k]['VisitProblem']=='No')
                    {
                        $totalGroupShopNoProblem++;
                        $totalRouteShopNoProblem++;
                    }
                        
                }
                $data[$i]['route'][$j]['ShopNoProblem']=$totalRouteShopNoProblem;
                $data[$i]['route'][$j]['ShopProblem']=$totalRouteShopProblem;
                
            }
            $data[$i]['ShopNoProblem']=$totalGroupShopNoProblem;
            $data[$i]['ShopProblem']=$totalGroupShopProblem;
        }
       
        return $data;
       
    }
    
    public function getData_Next_level2($saleRouteID,$tblXsisClientID,$est)
    {
        $data=  $this->getShop2($saleRouteID, $tblXsisClientID);
        
        for($i=0;$i<count($data);$i++){
            for($j=0;$j<count($data[$i]['shop']);$j++){
                $data[$i]['shop'][$j]['due']=$data[$i]['shop'][$j]['visitFrequency']-$data[$i]['shop'][$j]['lastvisit'];
            }
            $data[$i]=  $this->sort_Shop($data[$i]);
            $data[$i]['AVG']=  $this->getAverage_day($data[$i]['saleRouteID']);
        }
        
         //get sum shop problem when estimate=0;
        for($i=0;$i<count($data);$i++){
            $totalRouteShopProblem=0;
            $est_day=0;
            $check_increase=1;
            for($j=0;$j<count($data[$i]['shop']);$j++){
                if($data[$i]['shop'][$j]['due']<=$est){
                    $data[$i]['shop'][$j]['est']=$est_day;
                    //if due<est increase shopProblem
                    if($data[$i]['shop'][$j]['due']<$est_day){
                        $totalRouteShopProblem++;
                    }
                    $check_increase++;
                    if($check_increase>  ceil($data[$i]['AVG'])){
                        $est_day++;
                        $check_increase=1;
                    }
                }
            }
            $data[$i]['ShopProblem']=$totalRouteShopProblem;
        }
        return $data;
    }
    public function getData_Next_level3($saleGroupID,$tblXsisClientID,$est)
    {
        $data=  $this->getRoute($saleGroupID, $tblXsisClientID);
        
        for($i=0;$i<count($data);$i++){
            $data[$i]['shop']=  $this->getShop($data[$i]['saleRouteID'], $tblXsisClientID);
            for($j=0;$j<count($data[$i]['shop']);$j++){
                $data[$i]['shop'][$j]['due']=$data[$i]['shop'][$j]['visitFrequency']-$data[$i]['shop'][$j]['lastvisit'];
            }
            $data[$i]=  $this->sort_Shop($data[$i]);
            $data[$i]['AVG']=  $this->getAverage_day($data[$i]['saleRouteID']);
                    
        }
        
        //get sum shop problem when estimate=0;
        for($i=0;$i<count($data);$i++){
            $totalRouteShopProblem=0;
            $est_day=0;
            $check_increase=1;
            for($j=0;$j<count($data[$i]['shop']);$j++){
                if($data[$i]['shop'][$j]['due']<=$est){
                    $data[$i]['shop'][$j]['est']=$est_day;
                    //if due<est increase shopProblem
                    if($data[$i]['shop'][$j]['due']<$est_day){
                        $totalRouteShopProblem++;
                    }
                    $check_increase++;
                    if($check_increase>  ceil($data[$i]['AVG'])){
                        $est_day++;
                        $check_increase=1;
                    }
                }
            }
            $data[$i]['ShopProblem']=$totalRouteShopProblem;
        }
        
        return $data;
        
    }
   
    public function getData_Next_level4($tblXsisClientID,$est)
    {
      
        $data= $this->getGroup($tblXsisClientID);
        
        for($i=0;$i<count($data);$i++){
            //get saleroute in sale Group
            $data[$i]['route']=  $this->getRoute($data[$i]['saleGroupID'], $tblXsisClientID);
            for($j=0;$j<count($data[$i]['route']);$j++){
                
                //get shop in saleroute
                $data[$i]['route'][$j]['shop']=  $this->getShop($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID);
                //handle get next visit(in how many day next shop will have problem)
                $step=1;
                $max_position=  count($data[$i]['route'][$j]['shop'])-1;
                for($k=0;$k<=$max_position;$k++){
                    $data[$i]['route'][$j]['shop'][$k]['due']=$data[$i]['route'][$j]['shop'][$k]['visitFrequency'] - $data[$i]['route'][$j]['shop'][$k]['lastvisit'];
                    $due_change=$data[$i]['route'][$j]['shop'][$k]['due']+$data[$i]['route'][$j]['shop'][$k]['visitFrequency'];
                    while($due_change<$est){
                        $position=$max_position+$step;
                        $data[$i]['route'][$j]['shop'][$position]=$data[$i]['route'][$j]['shop'][$k];
                        $data[$i]['route'][$j]['shop'][$position]['due']=$due_change;
                        //increase due_change and step
                        $due_change+=$data[$i]['route'][$j]['shop'][$k]['visitFrequency'];
                        $step++;
                    }
                    
                }
                //sort Shop in route
                $data[$i]['route'][$j]=  $this->sort_Shop($data[$i]['route'][$j]);
                $data[$i]['route'][$j]['AVG']=  $this->getAverage_day($data[$i]['route'][$j]['saleRouteID']);
            }
        }
        
        //get sum shop problem when estimate = 0;
        
        
        for($i=0;$i<count($data);$i++){
            $totalGroupShopProblem=0;
            for($j=0;$j<count($data[$i]['route']);$j++){
                $totalRouteShopProblem=0;
                $est_day=0;
                $check_increase=1;
                for($k=0;$k<count($data[$i]['route'][$j]['shop']);$k++){
                    if($data[$i]['route'][$j]['shop'][$k]['due']<=$est){
                        $data[$i]['route'][$j]['shop'][$k]['est']=$est_day;
                        if($data[$i]['route'][$j]['shop'][$k]['due']<$est_day){
                            $totalGroupShopProblem++;
                            $totalRouteShopProblem++;
                        }
                        $check_increase++;
                        if($check_increase>ceil($data[$i]['route'][$j]['AVG'])){
                            $est_day++;
                            $check_increase=1;
                        }
                    }
                }
                $data[$i]['route'][$j]['ShopProblem']=$totalRouteShopProblem;
            }
            $data[$i]['ShopProblem']=$totalGroupShopProblem;
        }
        
        
        return $data;
        
        
    }
    public function getData_Stat_level2($saleRouteID,$tblXsisClientID,$days)
    {
       $data=  $this->getShop2($saleRouteID, $tblXsisClientID);
        for($i=0;$i<count($data);$i++){
            $data[$i]['sumShop']=0;
            $data[$i]['sumRep']=0;
            $data[$i]['sumLateFine']=0;
            $data[$i]['sumVisit']=0;
            
            $data[$i]['shop']=  $this->getShop($data[$i]['saleRouteID'], $tblXsisClientID);
            
            //set sum Shop
            $data[$i]['sumShop']=  count($data[$i]['shop']);
            
            //get sum Rep
            $data[$i]['sumRep']=  $this->getSumRep($data[$i]['saleRouteID'],$tblXsisClientID);
            
            //get sum LateFine
            $data[$i]['sumLateFine']=  $this->getSumLateFine($data[$i]['saleRouteID'],$tblXsisClientID,30);
            
            //get sum Visit
            $data[$i]['sumLateFine']=  $this->getSumVisit($data[$i]['saleRouteID'], $tblXsisClientID,30);
         
            
            
        }
        return $data;
    }
    public function getData_Stat_level3($tblSaleGroupID,$tblXsisClientID,$days)
    {
        $data=  $this->getRoute($tblSaleGroupID, $tblXsisClientID);
        for($i=0;$i<count($data);$i++){
            $data[$i]['sumShop']=0;
            $data[$i]['sumRep']=0;
            $data[$i]['sumLateFine']=0;
            $data[$i]['sumVisit']=0;
            
            $data[$i]['shop']=  $this->getShop($data[$i]['saleRouteID'], $tblXsisClientID);
            
            //set sum Shop
            $data[$i]['sumShop']=  count($data[$i]['shop']);
            
            //get sum Rep
            $data[$i]['sumRep']=  $this->getSumRep($data[$i]['saleRouteID'],$tblXsisClientID);
            
            //get sum LateFine
            $data[$i]['sumLateFine']=  $this->getSumLateFine($data[$i]['saleRouteID'],$tblXsisClientID,$days);
            
            //get sum Visit
            $data[$i]['sumLateFine']=  $this->getSumVisit($data[$i]['saleRouteID'], $tblXsisClientID,$days);
         
            
            
        }
        return $data;
    }
    public function getData_Stat_level4($tblXsisClientID,$days)
    {
        $data=  $this->getGroup($tblXsisClientID);
        for($i=0;$i<count($data);$i++)
        {
          $data[$i]['sumShop']=0;
          $data[$i]['sumRep']=0;
          $data[$i]['sumLateFine']=0;
           $data[$i]['sumVisit']=0;
          $data[$i]['route']=  $this->getRoute($data[$i]['saleGroupID'], $tblXsisClientID);
          for($j=0;$j<count($data[$i]['route']);$j++)
          {
              $data[$i]['route'][$j]['shop']=  $this->getShop($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID);
              
              //get sum of Shop
              $data[$i]['route'][$j]['sumShop']=count($data[$i]['route'][$j]['shop']);
              $data[$i]['sumShop']+=$data[$i]['route'][$j]['sumShop'];
              
              //get sum Rep
              $data[$i]['route'][$j]['sumRep']=  $this->getSumRep($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID);
              $data[$i]['sumRep']+=$data[$i]['route'][$j]['sumRep'];
              //get sum lateFine
              $data[$i]['route'][$j]['sumLateFine']=  $this->getSumLateFine($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID, $days);
              $data[$i]['sumLateFine']+=$data[$i]['route'][$j]['sumLateFine'];
              //get sum Visit
              $data[$i]['route'][$j]['sumVisit']=  $this->getSumVisit($data[$i]['route'][$j]['saleRouteID'], $tblXsisClientID, $days);
              $data[$i]['sumVisit']+=$data[$i]['route'][$j]['sumVisit'];
         
              
          }
        }
        return $data;
    }
    public function getGroup($tblXsisClientID)
    {
        $registry = Zend_Registry::getInstance();
        $DB = $registry['connectDB'];
        
        $sql="select distinct g.saleGroupID,g.`Desc`,u.repName,u.email
              from tblsalegroup g inner join tblsaleroute r on g.saleGroupID = r.saleGroupID inner join tblshop s 
              on r.saleRouteID = s.saleRouteID inner join tbluser u on r.saleRouteID=u.saleRouteID inner join tbljobcategory j on u.tblJobCategoryID= j.tblJobCategoryID
              where s.tblXsisClientID='".$tblXsisClientID."' and j.level='3' order by `desc`";
        
        $result=$DB->fetchAll($sql);
        
        return $result;
    }
    public function getRoute($tblSaleGroupID,$tblXsisClientID)
    {
        $registry = Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        
        $sql="select distinct r.saleRouteID,r.routeDesc
              from tblsaleroute r inner join tblshop s on r.saleRouteID=s.saleRouteID 
              where r.saleGroupID='".$tblSaleGroupID."' and s.tblXsisClientID='".$tblXsisClientID."'";
        
        $result=$DB->fetchAll($sql);
        
        return $result;
    }
    public function getShop($saleRouteID,$tblXsisClientID)
    {
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        
        $sql="select s.tblShopID, s.storeName,s.VisitProblem,s.lastvisit,c.visitFrequency,s.contact,s.storePhone
              from tblshop s inner join tblsetting st on s.storeClass=st.classID inner join tblclass c on s.storeClass=c.classID
            where s.saleRouteID='".$saleRouteID."' and s.tblXsisClientID='".$tblXsisClientID."' ";
        
        $result=$DB->fetchAll($sql);
        
        return $result;
        
    }
    public function getShop2($saleRouteID,$tblXsisClientID){
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        
        $sql="select saleRouteID,routeDesc from tblsaleroute where saleRouteID='".$saleRouteID."'";
        
        $result=$DB->fetchAll($sql);
        
         $sql="select s.tblShopID, s.storeName,s.VisitProblem,s.lastvisit,c.visitFrequency,s.contact,s.storePhone
              from tblshop s inner join tblsetting st on s.storeClass=st.classID inner join tblclass c on s.storeClass=c.classID
            where s.saleRouteID='".$saleRouteID."' and s.tblXsisClientID='".$tblXsisClientID."' ";
        $result[0]['shop']=$DB->fetchAll($sql);
        
        return $result;
        
    }


    public function getAverage_day($saleRouteID)
    {
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        $sum=0;
        $temp=7;
        $day=  date("Y-m-d");
        $day_end = date("Y-m-d",  strtotime("- ".$temp." day"));
        $sql="select count(*) as sum
            from tbltransactionraw r inner join tblshop s on r.tblshopID=s.tblshopID inner join tblsaleRoute sr on s.saleRouteID=sr.saleRouteID
            where sr.saleRouteID='".$saleRouteID."' and date between '".$day_end."' and '".$day."'";
        
        $result=$DB->fetchAll($sql);
        if(count($result)>0){
            $sum+=$result[0]['sum'];
        }
       /* for($i=1;$i<8;$i++){
           $day=date("Y-m-d",  strtotime("- ".$i." day"));
           
            
            $sql="select count(*) as sum
                  from tbltransactionraw r inner join tblshop s on r.tblShopID=s.tblShopID inner join tblsaleRoute sr on s.saleRouteID=sr.saleRouteID
                  where sr.saleRouteID='".$saleRouteID."' and date='".$day."'" ;
            $result=$DB->fetchAll($sql);
            
            if(count($result)>0){
            $sum+=$result[0]['sum'];
            }
        }*/
        //return number_format($sum/7,1,'.',',');
        return sprintf("%01.2f",$sum/7);
        
        
    }
    public function sort_Shop($array_route)
    {
        $temp_array;
        for($i=0;$i<count($array_route['shop'])-1;$i++){
           for($j=$i+1;$j<count($array_route['shop']);$j++){
               if($array_route['shop'][$i]['due'] > $array_route['shop'][$j]['due']){
                   $temp_array=$array_route['shop'][$i];
                   $array_route['shop'][$i]=$array_route['shop'][$j];
                   $array_route['shop'][$j]=$temp_array;
               }
           }
            
        }
        return $array_route;
    }
    
    public function getSumRep($saleRouteID,$tblXsisClientID){
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        
        $sql="select count(*) as sumRep 
            from tbluser u inner join tblsaleRoute r on u.saleRouteID=r.saleRouteID
            where u.tblXsisClientID='".$tblXsisClientID."' and u.saleRouteID='".$saleRouteID."'";
        
        $result=$DB->fetchAll($sql);
        if (count($result)>0){
            return $result[0]['sumRep'];
        }
        else 
        {
            return 0;
        }
        
    }
    public function getSumLateFine($saleRouteID,$tblXsisClientID,$days){
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        $sum=0;
        $day_end=date("Y-m-d");
        $day_start=date("Y-m-d",  strtotime("- ".$days." day"));
        
        $sql="select saleRouteID,lateFine
            from tblsummarydaily
            where tblXsisClientID='".$tblXsisClientID."' and saleRouteID='".$saleRouteID."' and date between '".$day_start."' and '".$day_end."'";
        $result=$DB->fetchAll($sql);
        
        if(count($result)>0){
            for($i=0;$i<count($result);$i++){
                $sum+=$result[$i]['lateFine'];
            }
        }
        return $sum;
    }
    public function getSumVisit($saleRouteID,$tblXsisClientID,$days){
        $registry=  Zend_Registry::getInstance();
        $DB=$registry['connectDB'];
        //$sum=0;
        $day_end=date("Y-m-d");
        $day_start=date("Y-m-d",  strtotime("- ".$days." day"));
        
        $sql="select count(*) as sum
              from tbltransactionraw t inner join tblshop s on t.tblShopID=s.tblShopID inner join tblsaleRoute r on s.saleRouteID=r.saleRouteID
              where r.saleRouteID='".$saleRouteID."' and s.tblXsisClientID='".$tblXsisClientID."' and t.date between '".$day_start."' and '".$day_end."'";
        $result=$DB->fetchAll($sql);
        
        return $result[0]['sum'];
    }
    public function getRep($tblXsisClientID){
        $DB=  Zend_Registry::get('connectDB');
        $sql="select tblRepID,repName
             from tbluser
             where tblXsisClientID='".$tblXsisClientID."'";
        
        $result=$DB->fetchAll($sql);
        return $result;
    }
}
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
?>
